package com.biye.api.tool.ExcelTemplate;

import com.alibaba.fastjson.JSONObject;
import com.base.common.edu.entity.Department;
import com.base.common.edu.entity.EduClass;
import com.base.common.edu.entity.Staff;
import com.base.common.edu.entity.Student;
import com.base.common.security.util.EncryptUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelImport {

    private final static String excel2003L = ".xls"; // 2003- 版本的excel
    private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel



    /***
     * 师生信息模板导入
     */
    public static Map<String, Object> readStuAndTeaInfoFromExcel(File file){
        Map<String, Object> resultMap = new HashMap<>();
        String msg = "文件解析失败！";
        boolean result = false;
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            resultMap.put("msg", msg);
            resultMap.put("result", result);
            return resultMap;
        }
        Workbook work = null;
        try{
            //获取excel文件流
            work = WorkbookFactory.create(fis);
            Sheet sheet = null;
            // 因为Excel不一定只有一张sheet，当前模板是根据SheetName来extract数据的
            List<String> departments = new ArrayList<>();
            // Teacher's Sheet Data
            Sheet tea_sheet = work.getSheet("Teacher");
            if (tea_sheet != null) {
                Row row;
                // 对表头进行校验
                String title = (String) PersonInfoTemplate.getCellValue(tea_sheet.getRow(0).getCell(0));
                if ( !"教职工信息导入模板".equals(title) ){
                    // 既然这个Sheet页存在，那么格式就一定要对，否则为什么要加这个Sheet页，单独导入Student‘s Sheet是可以的
                    resultMap.put("msg", "1表头信息不正确");
                    resultMap.put("result", result);
                    return resultMap;
                }
                // 判断列数8
                int column_count = tea_sheet.getRow(1).getLastCellNum();
                if ( column_count != 8 ){
                    // 既然这个Sheet页存在，那么格式就一定要对，否则为什么要加这个Sheet页，单独导入Student‘s Sheet是可以的
                    resultMap.put("msg", "1列信息不正确");
                    resultMap.put("result", result);
                    return resultMap;
                }
                // 声明一个列表来接收数据
                List<Staff> staff_list = new ArrayList<>();
                Staff staff;
                //遍历当前sheet的每一行，从第2（0、1、2...）行开始读取
                for (int j = 2; j < tea_sheet.getLastRowNum() + 1; j++) {
                    row = tea_sheet.getRow(j);
                    // 判断遇到空行则停止
                    if( PersonInfoTemplate.isRowEmpty(row) )
                        break;
                    staff = new Staff();
                    // 遍历所有的列
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                        if(y==0) staff.setTno((String) PersonInfoTemplate.getCellValue(row.getCell(y)));
                        if(y==1) staff.setName((String) PersonInfoTemplate.getCellValue(row.getCell(y)));
                        if(y==2) staff.setPasswd(EncryptUtil.getMD5String(EncryptUtil.getMD5String((String) PersonInfoTemplate.getCellValue(row.getCell(y)))));
                        if(y==3){
                            String sex_val = (String) PersonInfoTemplate.getCellValue(row.getCell(y));
                            int sex_val_ = "男".equals(sex_val) ? 1 : ("女".equals(sex_val)?2:0);
                            staff.setSex(sex_val_);
                        }
                        if(y==4) {
                            String idcard = (String) PersonInfoTemplate.getCellValue(row.getCell(y));
                            staff.setIdCard(idcard.replace("'", ""));
                        }
                        if(y==5) staff.setMobile((String) PersonInfoTemplate.getCellValue(row.getCell(y)));
                        if(y==6) staff.setEmail((String) PersonInfoTemplate.getCellValue(row.getCell(y)));
                        if(y==7){
                            String department_name = (String) PersonInfoTemplate.getCellValue(row.getCell(y));
                            Department d = new Department();
                            d.setDeptName(department_name);
                            if ( !departments.contains(department_name) )
                                departments.add(department_name);
                            staff.setDeptObject(d);
                        }
//                        System.out.print(PersonInfoTemplate.getCellValue(row.getCell(y))+" ");
                    }
                    staff_list.add(staff);
                    result = true;
                    msg = "excel解析成功";
                    resultMap.put("stafflist", staff_list);
                }
            }
            // Student's Sheet Data
            Sheet stu_sheet = work.getSheet("Student");
            if (stu_sheet != null) {
                Row row;
                // 对表头进行校验
                String title = (String) PersonInfoTemplate.getCellValue(stu_sheet.getRow(0).getCell(0));
                if ( !"学生信息导入模板".equals(title) ){
                    // 既然这个Sheet页存在，那么格式就一定要对，否则为什么要加这个Sheet页，单独导入Teacher‘s Sheet是可以的
                    resultMap.put("msg", "2表头信息不正确");
                    resultMap.put("result", result);
                    return resultMap;
                }
                // 判断列数11
                int column_count = stu_sheet.getRow(1).getLastCellNum();
                if ( column_count != 11 ) {
                    // 既然这个Sheet页存在，那么格式就一定要对，否则为什么要加这个Sheet页，单独导入Teacher‘s Sheet是可以的
                    resultMap.put("msg", "2列信息不正确");
                    resultMap.put("result", result);
                    return resultMap;
                }
                // 声明一个列表来接收数据
                List<Student> student_list = new ArrayList<>();
                // 声明一个列表接收     “班级&部门”
                List<String> classes = new ArrayList<>();
                Student student;
                EduClass eduClass;
                //遍历当前sheet的每一行，从第2（0、1、2...）行开始读取
                for (int j = 2; j < stu_sheet.getLastRowNum() + 1; j++) {
                    row = stu_sheet.getRow(j);
                    // 判断遇到空行则停止
                    if( PersonInfoTemplate.isRowEmpty(row) )
                        break;
                    student = new Student();
                    eduClass = new EduClass();
                    // 遍历所有的列
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                        if(y==0) student.setSno((String) PersonInfoTemplate.getCellValue(row.getCell(y)));
                        if(y==1) student.setStuName((String) PersonInfoTemplate.getCellValue(row.getCell(y)));
                        if(y==2) student.setPassword(EncryptUtil.getMD5String(EncryptUtil.getMD5String((String) PersonInfoTemplate.getCellValue(row.getCell(y)))));
                        if(y==3) {
                            String sex_val = (String) PersonInfoTemplate.getCellValue(row.getCell(y));
                            int sex_val_ = "男".equals(sex_val) ? 1 : ("女".equals(sex_val)?2:0);
                            student.setSex(sex_val_);
                        }
                        if(y==4) {
                            String idcard = (String) PersonInfoTemplate.getCellValue(row.getCell(y));
                            student.setIdCard(idcard.replace("'", ""));
                        }
                        if(y==5) student.setEmail((String) PersonInfoTemplate.getCellValue(row.getCell(y)));
                        if(y==6) {
                            StringBuffer stringBuffer = new StringBuffer();
                            stringBuffer.append(student.getEmail());  // phone
                            stringBuffer.append("###");  // separator
                            stringBuffer.append((String) PersonInfoTemplate.getCellValue(row.getCell(y)));  // email
                            student.setEmail(stringBuffer.toString());
                        }
                        // 7~10  入学年份，部门(系)，专业名称，班级编号
                        if(y==7) {
                            String grade_year = (String) PersonInfoTemplate.getCellValue(row.getCell(7));
                            eduClass.setGradeYear(grade_year);
                            Department d = new Department();
                            String department_name = (String) PersonInfoTemplate.getCellValue(row.getCell(8));
                            if ( !departments.contains(department_name) )
                                departments.add(department_name);
                            d.setDeptName(department_name);
                            eduClass.setDeptObject(d);
                            String class_name = (String) PersonInfoTemplate.getCellValue(row.getCell(9));
                            eduClass.setClassName(class_name);
                            String class_no = (String) PersonInfoTemplate.getCellValue(row.getCell(10));
                            eduClass.setClassNo(Integer.parseInt(class_no));
                            student.setEduClass(eduClass);
                            // 手机本excel上所有的班级信息
                            StringBuffer sb = new StringBuffer();
                            sb.append(class_name);
                            sb.append("@");
                            sb.append(grade_year);
                            sb.append("@");
                            sb.append(class_no);
                            sb.append("@");
                            sb.append(department_name);
                            if ( !classes.contains(sb.toString()) )
                                classes.add(sb.toString());
                            break;  // 跳出for "遍历所有的列"
                        }
//                        System.out.print(PersonInfoTemplate.getCellValue(row.getCell(y))+" ");
                    }
                    student_list.add(student);
                    result = true;
                    msg = "excel解析成功";
                    if ( classes.size() > 0 )
                        resultMap.put("classes", classes);
                    resultMap.put("studentlist", student_list);
                }
            }
            if ( departments.size() > 0 )
                resultMap.put("departments", departments);
            work.close();
        }catch(Exception e){
            /***
             * 由于Excel内容，如入学年份应为纯数字“2017”，若填错字符为“2017@qq.com”，则在数据格式转化时会失败
             * 仅捕获IOException这类异常并不能阻止接口报错，所以改为Exception
             */
            e.printStackTrace();
        }
        resultMap.put("msg", msg);
        resultMap.put("result", result);
        return resultMap;
    }


    /***
     * 读取Excel导入java代码模板  (弃用)
     * @param file
     * @return
     */
    public static JSONObject ImportA(File file) {
        JSONObject json=new JSONObject();
        String msg="读取Excel文件失败";
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        Workbook work = null;
        String fileType = file.getName().substring(file.getName().lastIndexOf("."));
        try{
            if (excel2003L.equals(fileType)) {
                work = new HSSFWorkbook(fis); // 2003-
            } else if (excel2007U.equals(fileType)) {
                work = new XSSFWorkbook(fis); // 2007+
            } else {
                json.put("status","解析的文件格式有误！");
                return json;
            }
            Sheet sheet = null;
            // 因为Excel不一定只有一张sheet，遍历Excel中所有的sheet
            for (int i = 0; i < work.getNumberOfSheets(); i++) {
                sheet = work.getSheetAt(i);
                if (sheet == null)
                    continue;
                //遍历当前sheet的每一行
                Row row ;
                for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
                    row = sheet.getRow(j);
                    if( PersonInfoTemplate.isRowEmpty(row) )
                        break;
                    // 遍历所有的列
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                        System.out.print(PersonInfoTemplate.getCellValue(row.getCell(y))+" ");
                    }
                    System.out.print("\n");
                }

            }
            work.close();
            msg="读取Excel文件成功";
        }catch(Exception e){
            e.printStackTrace();
        }
        json.put("status", msg);
        return json;
    }

}
